package data

import (
	"gitchina/kly_service/util"

	"database/sql"
	"github.com/astaxie/beego/orm"
	_ "github.com/go-sql-driver/mysql"
)

const (
	SQL_Mbr_Exists                                   = `SELECT 1 FROM m_member WHERE phone=? LIMIT 1`
	SQL_Mbr_Get_By_Phone                             = `SELECT id FROM m_member WHERE phone = ?`
	SQL_Mbr_Get_By_Vehicle_NO                        = `SELECT id FROM m_carrier_owner WHERE vehicle_no = ?`
	SQL_Mbr_Check_VerifyCode_Valid                   = `SELECT 1 FROM k_verify_code WHERE phone = ? AND code = ? AND is_valid = 1 AND create_time BETWEEN ? AND ? LIMIT 1`
	SQL_Mbr_Check_VerifyCode_Not_Sent                = `SELECT 1 FROM k_verify_code WHERE phone = ? AND create_time BETWEEN ? AND ? LIMIT 1`
	SQL_Mbr_Check_VerifyCode_Send_Phone_And_IP_Count = `SELECT (SELECT COUNT(1) FROM k_verify_code WHERE phone = ? AND create_time BETWEEN ? AND ?) AS phone_count, (SELECT COUNT(1) FROM k_verify_code WHERE client_ip = ? AND create_time BETWEEN ? AND ?) AS ip_count`
	SQL_Mbr_Invalid_VerifyCode                       = `UPDATE k_verify_code SET is_valid = 0 WHERE phone = ? AND code = ?`
	SQL_Mbr_Insert_VerifyCode                        = "INSERT INTO k_verify_code(phone, `code`, is_valid, hour_part, client_ip) VALUES(?,?,?,?,?)"
	SQL_Mbr_Check_Phone_And_Passwd                   = `SELECT id, name, nick_name, phone, type_id, level, auth_level, photo, qrcode, id_card_no, state, inviter_id FROM m_member WHERE phone = ?  AND password = ? LIMIT 1`
	SQL_Mbr_Get_Member_Type                          = `SELECT type_id FROM m_member WHERE id = ?`
	SQL_Mbr_Update_Member_Type                       = `UPDATE m_member SET type_id=? WHERE id = ?`
	SQL_Mbr_Upd_PhotoUrl                             = `UPDATE m_member SET photo = ? WHERE id = ? `
	SQL_Mbr_Upd_QRCode                               = `UPDATE m_member SET qrcode= ? WHERE id= ?`
	SQL_Mbr_Upd_Passwd_By_Phone                      = `UPDATE m_member SET password=? WHERE phone=?`
	SQL_Mbr_Upd_Passwd_By_Passwd                     = `UPDATE m_member SET password=? WHERE id = ? AND password=?`
	SQL_Mbr_Carrier_Owner_Exists                     = `SELECT 1 FROM m_carrier_owner WHERE id=?`
	SQL_Mbr_Cargo_Owner_Exists                       = `SELECT 1 FROM m_cargo_owner WHERE id=?`
	SQL_Mbr_Upd_Member_ID_Card_FS                    = `UPDATE m_member SET id_card_fs = ? WHERE id = ?`
	SQL_Mbr_Upd_Member_ID_Card_RS                    = `UPDATE m_member SET id_card_rs = ? WHERE id = ?`
	SQL_Mbr_Upd_Carrier_Owner_Vehicle_PhotoUrl       = `UPDATE m_carrier_owner SET vehicle_photo = ? WHERE id = ? `
	SQL_Mbr_Upd_Carrier_Owner_Driving_License        = `UPDATE m_carrier_owner SET driving_license = ? WHERE id = ? `
	SQL_Mbr_Upd_Carrier_Owner_Vehicle_License_1      = `UPDATE m_carrier_owner SET vehicle_license_1 = ? WHERE id = ? `
	SQL_Mbr_Upd_Carrier_Owner_Vehicle_License_2      = `UPDATE m_carrier_owner SET vehicle_license_2 = ? WHERE id = ? `
	SQL_Mbr_Upd_Cargo_Owner_Biz_License              = `UPDATE m_cargo_owner SET biz_license = ? WHERE id = ?`
	SQL_Mbr_Upd_Emp_Biz_License                      = `UPDATE m_logistics_emp SET biz_license = ? WHERE id = ?`
	SQL_Mbr_Upd_Emp_Biz_Permit                       = `UPDATE m_logistics_emp SET biz_permit = ? WHERE id = ?`
	SQL_Mbr_Upd_Auth_State                           = `UPDATE m_member SET auth_state = 1 WHERE id = ? AND LENGTH(id_card_fs) >=4 AND LENGTH(id_card_rs) >= 4`
	SQL_Mbr_Upd_Name_And_IDCardNo                    = `UPDATE m_member SET name = ?, nick_name = ?, id_card_no = ? WHERE id = ?`
	SQL_Mbr_Upd_Carrier_Owner_Auth_State             = `UPDATE m_carrier_owner SET auth_state = 1 WHERE id = ? AND LENGTH(vehicle_photo) >= 4 AND LENGTH(driving_license) >= 4 AND LENGTH(vehicle_license_1) >= 4 AND LENGTH(vehicle_license_2) >= 4`
	SQL_Mbr_Upd_Enti_Cargo_Owner_Auth_State          = `UPDATE m_cargo_owner SET auth_state = 1 WHERE id = ?`
	SQL_Mbr_Upd_Personal_Cargo_Owner_Auth_State      = `UPDATE m_cargo_owner SET auth_state = 1 WHERE id = ? AND is_corp = 0`
	SQL_Mbr_Upd_Emp_Auth_State                       = `UPDATE m_logistics_emp SET auth_state = 1 WHERE id = ? AND LENGTH(biz_license) >= 4 AND LENGTH(biz_permit) >= 4`
	SQL_Mbr_Get_Personal_Auth_State                  = `SELECT auth_state, type_id, state_reason FROM m_member WHERE id = ?`
	SQL_Mbr_Get_Enti_Auth_State                      = `SELECT auth_state FROM %s WHERE id = ?`
	SQL_Mbr_Get_Personal_Auth_Info                   = `SELECT id_card_fs, id_card_rs FROM m_member WHERE id = ?`
	SQL_Mbr_Add_Role                                 = `INSERT INTO %s(id, auth_state) VALUES(?, 1)`
	SQL_Mbr_Role_Exists                              = `SELECT 1 FROM %s WHERE id = ?`
	SQL_Mbr_Get_Vehicle_Info                         = `SELECT vehicle_type, vehicle_load, vehicle_len FROM m_carrier_owner WHERE id = ?`
	SQL_Mbr_Get_Carrier_Owner                        = `SELECT name, stars, phone, m.create_time, m.photo, score_total, score_count, vehicle_type, vehicle_len, vehicle_load, vehicle_no, vehicle_photo, driving_license, vehicle_license_1, vehicle_license_2, id_card_no, frequent_line_start, frequent_line_end, ship_name, ship_mmsi, ship_ex_date, ship_harbour, ship_width, ship_depth, ship_capacity, ship_full_load, ship_empty_load, ship_hatchway FROM m_carrier_owner c INNER JOIN m_member m ON c.id = m.id WHERE c.id = ?`
	SQL_Mbr_Get_Cargo_Owner                          = `SELECT name, phone, is_corp, corp_name, corp_addr, telephone, biz_license FROM m_cargo_owner c INNER JOIN m_member m ON c.id = m.id WHERE c.id = ?`
	SQL_Mbr_Emp_Exists                               = `SELECT 1 FROM m_logistics_emp WHERE id = ?`
	SQL_Mbr_Ensure_Cargo_Owner                       = `SELECT 1 FROM m_member WHERE id = ? AND state IN (0) AND auth_state = 2 AND type_id IN (1, 3)`
	SQL_Mbr_Ensure_Carrier_Owner                     = `SELECT 1 FROM m_member WHERE id = ? AND state IN (0) AND auth_state = 2 AND type_id IN (2, 4)`
	SQL_Mbr_Get_RegisterId                           = `SELECT register_id, platform FROM m_login_log WHERE member_id = ? ORDER BY id DESC LIMIT 1`
	SQL_Mbr_Upd_Client_Info                          = `UPDATE m_member SET register_id = ?, client_platform = ? WHERE id = ?`
	SQL_Mbr_Get_Brief_Info                           = `SELECT id, name, nick_name, photo, phone FROM m_member WHERE id IN(%s)`
	SQL_Mbr_Score_Update                             = `UPDATE m_member SET score_total = score_total + %d, score_count = score_count + 1 WHERE id = ?`
	SQL_Mbr_Score_Update2                            = `UPDATE m_member SET score_total = score_total + %d - 5 WHERE id = ?`
	SQL_Mbr_Get_Last_Point_Log                       = `SELECT type, extra FROM t_point_log WHERE member_id = ? AND type = ? ORDER BY id DESC LIMIT 1`
	SQL_Mbr_Update_Point                             = `UPDATE m_member SET point = point + ? WHERE id = ?`
	SQL_Mbr_Score_Order_Count                        = "SELECT (SELECT score_count FROM m_member WHERE id = ?) AS score_count, (SELECT COUNT(1) FROM o_waybill WHERE carrier_owner_id = ? AND state = 7) AS order_count"
	SQL_Logis_Get_Area                               = `SELECT id, name FROM s_area`
	SQL_Logis_Last_Carrier_Pub                       = `SELECT id, state FROM l_carrier_pub WHERE member_id = ? ORDER BY id DESC LIMIT 1`
	SQL_Logis_Carrier_Pub_Count                      = `SELECT COUNT(1) FROM l_carrier_pub WHERE member_id = ? AND state = 0`
	SQL_Logis_Active_Carrier_Pub_Count               = `SELECT COUNT(1) FROM l_carrier_pub WHERE member_id = ? AND state = 1`
	SQL_Logis_Upd_Carrier_Pub                        = `UPDATE l_carrier_pub SET src_province = ?, src_city = ?, src_county = ?, dest_province = ?, dest_city = ?, dest_county = ?, price = ?, shipment_time = ?, vehicle_load = ?, src_geo = ?, dest_geo = ?, src_location_title = ?, dest_location_title = ?, src_location = ?, dest_location = ? WHERE id = ? AND member_id = ? AND state = 0`
	SQL_Logis_Get_Carrier_Pub                        = `SELECT id, src_province, src_city, src_county, dest_province, dest_city, dest_county, shipment_time, vehicle_load, price, state, src_geo, type FROM l_carrier_pub WHERE member_id = ? AND state IN (0, 1) ORDER BY id DESC LIMIT 1`
	SQL_Logis_Get_Active_Carrier_Pub                 = `SELECT id, src_province, src_city, src_county, dest_province, dest_city, dest_county, shipment_time, vehicle_load, price, state, src_geo, dest_geo, src_location_title, dest_location_title, src_location, dest_location, type FROM l_carrier_pub WHERE member_id = ? AND state = 0 ORDER BY id DESC LIMIT 3`
	SQL_Logis_Get_Carrier_Pub_History                = `SELECT id, src_province, src_city, src_county, dest_province, dest_city, dest_county, shipment_time, vehicle_load, price, state, src_geo, dest_geo, src_location_title, dest_location_title, src_location, dest_location, type FROM l_carrier_pub WHERE member_id = ? AND state <> 3 ORDER BY id DESC LIMIT 5`
	SQL_Logis_Get_Corp_Exists                        = `SELECT id FROM l_logistics WHERE name = ?`
	SQL_Logis_Get_Carrier_Pub_List                   = `SELECT p.id AS pub_id, p.member_id, m.name, m.phone, stars, src_province, src_city, src_county, dest_province, dest_city, dest_county, price, shipment_time, p.vehicle_type, p.vehicle_load, p.vehicle_len, vehicle_photo, src_geo, dest_geo, src_location_title, dest_location_title, src_location, dest_location, type FROM l_carrier_pub p INNER JOIN m_carrier_owner c ON p.member_id = c.id INNER JOIN m_member m ON c.id = m.id WHERE p.state = 0 %s`
	SQL_Logis_Get_Carrier_Pub_List_Count             = `SELECT COUNT(1) AS rowCount FROM l_carrier_pub p INNER JOIN m_carrier_owner c ON p.member_id = c.id INNER JOIN m_member m ON c.id = m.id WHERE p.state = 0 %s`
	SQL_Logis_Get_Carrier_List                       = `SELECT m.id AS member_id, m.name, m.auth_state, m.create_time, photo, phone, stars, score_total, score_count, vehicle_type, vehicle_load, vehicle_len, vehicle_photo, frequent_line_start, frequent_line_end FROM m_carrier_owner p INNER JOIN m_member m ON p.id = m.id WHERE m.state = 0 %s`
	SQL_Logis_Get_Carrier_List_Count                 = `SELECT COUNT(1) AS rowCount FROM m_carrier_owner p INNER JOIN m_member m ON p.id = m.id WHERE m.state = 0 %s`
	SQL_Logis_Get_Row_Count                          = `SELECT FOUND_ROWS()`
	SQL_Logis_Get_Logistics_Corp                     = `SELECT m.name, l.name AS corp_name, addr AS corp_addr, telephone, remark, area_id, l.photo, em.biz_license, em.biz_permit FROM m_logistics_emp em LEFT JOIN l_logistics l ON l.id = em.corp_id INNER JOIN m_member m ON m.id = em.id WHERE m.id = ?`
	SQL_Logis_Get_Carrier_Pub_Detail                 = `SELECT p.id AS pub_id, m.id AS member_id, m.name, m.phone, m.photo, stars, src_province, src_city, src_county, dest_province, dest_city, dest_county, shipment_time, p.vehicle_type, p.vehicle_len, p.vehicle_load, vehicle_no, price, src_geo, dest_geo, src_location_title, dest_location_title, src_location, dest_location, frequent_line_start, frequent_line_end, type FROM l_carrier_pub p INNER JOIN m_carrier_owner c ON p.member_id = c.id INNER JOIN m_member m ON c.id = m.id WHERE p.id = ?`
	SQL_Logis_Last_Line_Pub                          = `SELECT id FROM l_line_pub WHERE member_id = ? AND state = 0 AND src_province = ? AND src_city = ? AND src_county = ? AND dest_province = ? AND dest_city = ? AND dest_county = ? ORDER BY id DESC LIMIT 1`
	SQL_Logis_Get_Line_Pub                           = `SELECT id, src_province, src_city, src_county, dest_province, dest_city, dest_county, term_of_validity, heavy_cargo_price, light_cargo_price, base_price, state FROM l_line_pub WHERE member_id = ? %s ORDER BY id DESC LIMIT %d, %d`
	SQL_Logis_Get_Line_Pub_Count                     = `SELECT COUNT(1) AS rowCount FROM l_line_pub WHERE member_id = ? %s`
	SQL_Logis_Update_Line                            = `UPDATE l_line_pub SET src_province = ?, src_city = ?, src_county = ?, dest_province = ?, dest_city = ?, dest_county = ?, term_of_validity = ?, heavy_cargo_price = ?, light_cargo_price = ?, base_price = ? WHERE id = ? AND member_id = ? AND state = 0`
	SQL_Logis_Get_Line_Pub_List                      = `SELECT p.id AS pub_id, p.member_id, m.name,  m.phone, m.photo, l.name AS corp_name, src_province, src_city, src_county, dest_province, dest_city, dest_county, term_of_validity, heavy_cargo_price, light_cargo_price, base_price FROM l_line_pub p INNER JOIN m_logistics_emp e ON p.member_id = e.id INNER JOIN m_member m ON e.id = m.id INNER JOIN l_logistics l ON e.corp_id = l.id WHERE p.state = 0 %s`
	SQL_Logis_Get_Line_Pub_List_Count                = `SELECT COUNT(1) AS rowCount FROM l_line_pub p INNER JOIN m_logistics_emp e ON p.member_id = e.id INNER JOIN m_member m ON e.id = m.id INNER JOIN l_logistics l ON e.corp_id = l.id WHERE p.state = 0 %s`
	SQL_Logis_Get_Line_Pub_Detail                    = `SELECT p.id AS pub_id, p.member_id, m.name,  m.phone, m.photo, l.name AS corp_name, intro, src_province, src_city, src_county, dest_province, dest_city, dest_county, term_of_validity, heavy_cargo_price, light_cargo_price, base_price, p.state FROM l_line_pub p INNER JOIN m_logistics_emp e ON p.member_id = e.id INNER JOIN m_member m ON e.id = m.id INNER JOIN l_logistics l ON e.corp_id = l.id WHERE p.id = ?`
	SQL_Logis_Get_Invoice_Corps                      = `SELECT id, name, area_id, addr FROM l_invoice_corp`
	SQL_Logis_Get_Invoice_Addrs                      = `SELECT id, title, addr, recv_name, recv_phone, zip_code, tax_no, bank_of_deposit, bank_card_no FROM l_invoice_addr WHERE member_id = ?`
	SQL_Logis_Ensure_Invoice_Corp_Exists             = `SELECT 1 FROM l_invoice_corp WHERE id = ?`
	SQL_Logis_Get_Last_Invoice_Addr_1                = `SELECT title, post_addr as addr, recv_name, recv_phone, zip_code, tax_no, bank_of_deposit, bank_card_no, amount, member_id, corp_id, state FROM o_invoice WHERE member_id=? ORDER BY id DESC LIMIT 1`
	SQL_Logis_Get_Last_Invoice_Addr_2                = `SELECT title, addr, recv_name, recv_phone, zip_code, tax_no, bank_of_deposit, bank_card_no FROM l_invoice_addr WHERE member_id=? ORDER BY id DESC LIMIT 1`
	SQL_Logis_Update_Invoice_Addr                    = `UPDATE l_invoice_addr SET title = ?, addr = ?, recv_name = ?, recv_phone = ?, zip_code = ?, tax_no = ?, bank_of_deposit = ?, bank_card_no = ? WHERE id = ? AND member_id = ?`
	SQL_Logis_Delete_Invoice_Addr                    = `DELETE FROM l_invoice_addr WHERE id = ? AND member_id = ?`
	SQL_Logis_Cancel_Line                            = `UPDATE l_line_pub SET state = 1 WHERE id = ? AND member_id = ?`
	SQL_Logis_Recover_Line                           = `UPDATE l_line_pub SET state = 0 WHERE id = ? AND member_id = ?`
	SQL_Logis_Cancel_Carrier_Pub                     = `UPDATE l_carrier_pub SET state = 3 WHERE id = ? AND member_id = ? AND state = 0`
	SQL_Logis_Update_Logistics_Biz                   = `UPDATE m_logistics_emp SET intro = ?, service_type = ?, biz_addr = ? WHERE id = ?`
	SQL_Logis_Get_Logistics_Biz                      = `SELECT intro, biz_addr, service_type FROM m_logistics_emp WHERE id = ?`
	SQL_Logis_Get_Recommend_Line                     = `SELECT ll.id, m.photo AS emp_photo_url, l.name AS corp_name, ll.src_province, ll.src_city, ll.src_county, ll.dest_province, ll.dest_city, ll.dest_county  FROM l_recommend_line lr INNER JOIN l_line_pub ll ON lr.line_pub_id = ll.id INNER JOIN m_logistics_emp ml ON ll.member_id = ml.id INNER JOIN m_member m ON m.id = ml.id INNER JOIN l_logistics l ON l.id = ml.corp_id WHERE lr.enabled = 1 LIMIT %d` //omit filter
	//SQL_Logis_Get_Recommend_Line                 = `SELECT ll.id, m.photo AS emp_photo_url, l.name AS corp_name, ll.src_province, ll.src_city, ll.src_county, ll.dest_province, ll.dest_city, ll.dest_county  FROM l_recommend_line lr INNER JOIN l_line_pub ll ON lr.line_pub_id = ll.id INNER JOIN m_logistics_emp ml ON ll.member_id = ml.id INNER JOIN m_member m ON m.id = ml.id INNER JOIN l_logistics l ON l.id = ml.corp_id WHERE lr.enabled = 1 AND lr.start_area = ? LIMIT %d`
	SQL_Logis_Get_Logistics_Corp_Detail          = `SELECT e.id AS owner_id, m.name AS owner_name,  m.phone AS owner_phone, m.photo AS owner_photo, l.name AS corp_name, intro FROM m_logistics_emp e INNER JOIN m_member m ON e.id = m.id INNER JOIN l_logistics l ON e.corp_id = l.id WHERE e.id = ?`
	SQL_Logis_Get_Corp_List                      = `SELECT m.name AS owner_name, m.phone AS owner_phone, m.photo AS owner_photo, m.id AS owner_id, l.name AS corp_name, intro FROM l_logistics l INNER JOIN m_logistics_emp em ON l.id = em.corp_id INNER JOIN m_member m ON m.id = em.id WHERE l.area_id = ? LIMIT %d, %d`
	SQL_Logis_Get_Corp_List_Count                = `SELECT COUNT(1) AS rowCount FROM l_logistics l INNER JOIN m_logistics_emp em ON l.id = em.corp_id INNER JOIN m_member m ON m.id = em.id WHERE l.area_id = ?`
	SQL_Logis_Query_Corp_List                    = `SELECT m.name AS owner_name, m.phone AS owner_phone, m.photo AS owner_photo, m.id AS owner_id, l.name AS corp_name, intro FROM l_logistics l INNER JOIN m_logistics_emp em ON l.id = em.corp_id INNER JOIN m_member m ON m.id = em.id WHERE l.area_id LIKE '%s' LIMIT %d, %d`
	SQL_Logis_Query_Corp_List_Count              = `SELECT COUNT(1) AS rowCount FROM l_logistics l INNER JOIN m_logistics_emp em ON l.id = em.corp_id INNER JOIN m_member m ON m.id = em.id WHERE l.area_id LIKE '%s'`
	SQL_Logis_Can_Log_Transport                  = `SELECT 1 FROM l_transport WHERE waybill_id = ? AND create_time > ?`
	SQL_Logis_Get_Transport_Logs                 = `SELECT lng, lat, create_time, id FROM l_transport WHERE waybill_id = ? AND id >= ?`
	SQL_Logis_Get_Last_Transport_Log             = `SELECT lng, lat, create_time, id FROM l_transport WHERE waybill_id = ? ORDER BY id DESC LIMIT 1`
	SQL_Logis_Active_Expired_Carrier             = `UPDATE l_carrier_pub SET shipment_time = now() WHERE shipment_time < ? AND state = 0`
	SQL_Logis_Use_OildCard                       = `UPDATE o_waybill SET need_oil_card = ?, oilcard_no = ? WHERE id = ? AND carrier_owner_id = ?`
	SQL_Order_Ensure_Only_One_Waybill            = `SELECT id FROM o_waybill WHERE cargo_owner_id = ? AND carrier_pub_id = ? AND state NOT IN(7, 10) LIMIT 1`
	SQL_Order_Freeze_Carrier_Pub                 = `UPDATE l_carrier_pub SET state = 1 WHERE id = ?`
	SQL_Order_Get_Waybill_By_Cargo_Owner         = `SELECT id, start_area, end_area, cargo_type, cargo_name, price, deposit, shipment_time, state, payment_term FROM o_waybill WHERE cargo_owner_id = ? %s ORDER BY id DESC LIMIT %d, %d`
	SQL_Order_Get_Waybill_By_Cargo_Owner_Count   = `SELECT COUNT(1) AS rowCount FROM o_waybill WHERE cargo_owner_id = ? %s`
	SQL_Order_Get_Waybill_By_Carrier_Owner       = `SELECT id, start_area, end_area, cargo_type, cargo_name, price, deposit, shipment_time, state, payment_term FROM o_waybill WHERE carrier_owner_id = ? %s ORDER BY id DESC LIMIT %d, %d`
	SQL_Order_Get_Waybill_By_Carrier_Owner_Count = `SELECT COUNT(1) AS rowCount FROM o_waybill WHERE carrier_owner_id = ? %s`
	SQL_Order_Get_Waybill_Detail                 = `SELECT cargo_owner_id, mm.name AS carrier_owner, mm.phone AS carrier_owner_phone, carrier_owner_id,  carrier_pub_id, w.shipment_time, start_area, start_addr, end_area, end_addr, w.src_geo, w.dest_geo, w.src_location_title, w.dest_location_title, w.src_location, w.dest_location, cargo_name, cargo_type, cargo_bulk, cargo_weight, deposit, w.price,consignor, w.state,vehicle_no, l.vehicle_type, l.vehicle_len, l.vehicle_load, w.remark, need_invoice, need_insurance, payment_term, arrival_time, cargo_count, cargo_value, consignee, consignee_phone, need_oil_card, oilcard_no, LEFT(w.create_time, 10) AS order_time FROM o_waybill w INNER JOIN l_carrier_pub l ON w.carrier_pub_id = l.id INNER JOIN m_carrier_owner m ON m.id = w.carrier_owner_id INNER JOIN m_member mm ON mm.id = w.carrier_owner_id WHERE w.id = ?`
	SQL_Order_Update_Waybill                     = `UPDATE o_waybill SET shipment_time = ?, start_area = ?, start_addr = ?, end_area = ?, end_addr = ?, cargo_name = ?, cargo_type = ?, cargo_bulk = ?, cargo_weight = ?, deposit = ?, price = ?, consignor = ?, remark = ?, cargo_count = ?, cargo_value = ?, consignee = ?, consignee_phone = ?, arrival_time = ? WHERE id = ? AND cargo_owner_id = ? AND state IN (0, 1, 2, 4)`
	SQL_Order_Switch_Deposit_Paid                = `UPDATE o_waybill SET state = 1 WHERE id = ? AND state = 0 AND cargo_owner_id = ? `
	SQL_Order_Switch_OnTheWay                    = `UPDATE o_waybill SET state = 2 WHERE id = ? AND state = 1 AND carrier_owner_id = ? `
	SQL_Order_Switch_OnContract_Wait2Pay         = `UPDATE o_waybill SET state = 3, need_invoice = ?, need_insurance = ?, payment_term = ? WHERE id = ? AND state IN (2, 3) AND cargo_owner_id = ? `
	SQL_Order_Switch_OnContract_Wait2Confirm     = `UPDATE o_waybill SET state = 4 WHERE id = ? AND state = 3 AND cargo_owner_id = ? `
	SQL_Order_Switch_OnContract_Offline          = `UPDATE o_waybill SET state = 4, need_invoice = 1 WHERE id = ? AND state = 2 AND cargo_owner_id = ? `
	SQL_Order_Switch_OnShipment                  = `UPDATE o_waybill SET state = 5 WHERE id = ? AND state = 4 AND carrier_owner_id = ? `
	SQL_Order_Switch_OnArrival                   = `UPDATE o_waybill SET state = 6 WHERE id = ? AND state = 5 AND carrier_owner_id = ? `
	SQL_Order_Switch_Complete                    = `UPDATE o_waybill SET state = 7 WHERE id = ? AND state IN(5, 6) AND cargo_owner_id = ? `
	SQL_Order_Switch_Cancel                      = `UPDATE o_waybill SET state = 10 WHERE id = ? AND state <= 4 AND (cargo_owner_id = ? OR carrier_owner_id = ?)`
	SQL_Order_Set_Carrier_Pub                    = `UPDATE l_carrier_pub SET state = %d WHERE id = ?`
	SQL_Order_Get_Pub_Id_By_Waybill_Id           = `SELECT carrier_pub_id FROM o_waybill WHERE id = ?`
	SQL_Order_Get_Invoice                        = `SELECT id, title, post_addr, recv_name, amount, member_id, corp_id, bank_of_deposit, bank_card_no, state FROM o_invoice WHERE waybill_id=?`
	SQL_Order_Get_Linebill_By_Owner              = `SELECT id, start_area, end_area, cargo_type, shipment_time, state FROM o_linebill WHERE owner_id = ? %s ORDER BY id DESC LIMIT %d, %d`
	SQL_Order_Get_Linebill_By_Owner_Count        = `SELECT COUNT(1) AS rowCount FROM o_linebill WHERE owner_id = ? %s`
	SQL_Order_Get_Linebill_By_Emp                = `SELECT id, start_area, end_area, cargo_type, shipment_time, state FROM o_linebill WHERE emp_id = ? %s ORDER BY id DESC LIMIT %d, %d`
	SQL_Order_Get_Linebill_By_Emp_Count          = `SELECT COUNT(1) AS rowCount FROM o_linebill WHERE emp_id = ? %s`
	SQL_Order_Upd_Linebill_State                 = `UPDATE o_linebill SET state = ? WHERE id = ? AND emp_id = ?`
	SQL_Order_Ensure_Can_Make_Linebill           = `SELECT 1 FROM l_line_pub WHERE id = ? AND state = 0`
	SQL_Order_Get_Letter_Of_Authorization        = `SELECT m.name AS carrier_owner, m.id_card_no AS id_card_no, vehicle_no, LEFT(o.create_time, 10) AS order_time FROM o_waybill o INNER JOIN l_carrier_pub p ON o.carrier_pub_id = p.id INNER JOIN m_carrier_owner c ON o.carrier_owner_id = c.id INNER JOIN m_member m ON c.id = m.id WHERE o.id = ? AND o.need_invoice = 1`
	SQL_Order_Get_Invoice_Info                   = `SELECT o.id, title, post_addr, recv_name, tax_no, bank_of_deposit,bank_card_no, zip_code, recv_phone, amount, io.name AS invoice_corp FROM o_invoice o INNER JOIN l_invoice_corp io ON o.corp_id = io.id WHERE waybill_id = ? LIMIT 1`
	SQL_Order_Get_Waybill_Owner                  = `SELECT cargo_owner_id, carrier_owner_id, consignor, consignee, consignee_phone, arrival_time, start_addr, end_addr FROM o_waybill WHERE id = ?`
	SQL_Order_Get_Waybill_State                  = `SELECT state, update_time FROM o_waybill WHERE id = ?`
	SQL_Order_Get_Not_Complete_Waybills          = `SELECT id, cargo_owner_id, carrier_owner_id FROM o_waybill WHERE state = 6 AND create_time < '%s'`
	SQL_Order_Get_Contract_Time                  = `SELECT create_time FROM o_contract WHERE waybill_id = ? LIMIT 1`
	SQL_Order_Score_Update                       = `UPDATE o_waybill_score SET score = ?, comment = ?, is_issued = 1 WHERE waybill_id = ? AND commenter_id = ? AND is_issued = 0`
	SQL_Order_Get_Score_List                     = `SELECT nick_name, score, comment, o.create_time FROM m_member m INNER JOIN o_waybill_score o ON m.id = o.commenter_id WHERE o.carrier_owner_id = ? ORDER BY o.id DESC`
	SQL_Order_Invoice_Complete                   = `UPDATE o_invoice set state = 1 WHERE waybill_id = ? AND member_id = ?`
	SQL_Order_Exists                             = `SELECT id FROM o_waybill WHERE carrier_owner_id = ? AND LEFT(shipment_time, 10) = ? AND LEFT(create_time, 10) = ? AND price = ? AND remark = ? ORDER BY id DESC LIMIT 1`
	SQL_Social_Add_Chat_Log                      = `INSERT INTO chat_log(session_id, text, sender_id, message_id, media_type) SELECT id, '%s', '%s', '%s', %d FROM chat_session WHERE session_key = ?`
	SQL_Social_Get_Chat_Logs                     = `SELECT id, text, sender_id, message_id, media_type, create_time FROM chat_log WHERE session_id = ? AND create_time > ? AND id > ?`
	SQL_Social_Get_Participant_Session_Id        = `SELECT session_key FROM chat_participant WHERE participant_id = ? ORDER BY id DESC LIMIT %d, %d`
	SQL_Social_Get_All_Participants              = `SELECT session_key, participants FROM chat_session WHERE session_key IN (%s)`
	SQL_General_Get_Dicts                        = `SELECT name, code, parent_id, sort_num FROM s_dict ORDER BY parent_id, sort_num`
	SQL_General_Get_Paras                        = `SELECT para_key, para_value FROM s_para`
	SQL_General_Get_Ads                          = `SELECT pic_url, html_url, sort_num FROM k_ad WHERE enabled = 1 ORDER BY sort_num LIMIT %d`
	SQL_General_Check_ClientId_Exists            = `SELECT 1 FROM k_device WHERE client_id=?`
	SQL_General_Get_Blocked_Ips                  = `SELECT client_ip FROM r_black_ip`
	SQL_General_Get_Bank_List                    = `SELECT id, name FROM t_bank WHERE enable = 1`
	SQL_Wallet_Withdraw_List                     = `SELECT amount, amount_after_ratio, card_no, card_type, bank_name, phone, province, city, state, remark, t.create_time FROM t_withdraw t WHERE t.member_id= ? ORDER BY t.id DESC LIMIT %d, %d`
	SQL_Wallet_Withdraw_List_Count               = `SELECT COUNT(1) FROM t_withdraw WHERE member_id= ?`
	SQL_Wallet_Update_QPay_BankCard_Undecoded    = "UPDATE t_bank_card SET undecoded = ? WHERE id = ?"
	SQL_Wallet_Set_QPay_BankCard_Apply_OK        = "UPDATE t_bank_card SET state = 1, params = ?, undecoded = ? WHERE id = ?"
	SQL_Wallet_Set_QPay_BankCard_Confirm_OK      = "UPDATE t_bank_card SET partner_id = ?, state = 2, undecoded = ? WHERE id = ?"
	SQL_QPay_Update_Undecoded                    = "UPDATE t_qpay SET undecoded = ? WHERE id = ?"
	SQL_QPay_Apply_OK                            = "UPDATE t_qpay SET state = 1, params = ?, undecoded = ? WHERE id = ?"
	SQL_QPay_Confirm_OK                          = "UPDATE t_qpay SET state = 2, undecoded = ? WHERE id = ?"
	SQL_Invoice_SUM                              = `SELECT IFNULL(SUM(amount),0) FROM o_invoice o INNER JOIN o_waybill b ON o.waybill_id = b.id WHERE b.state <> 10`
	SQL_Get_Invoice_Issue_Corp                   = "SELECT name FROM l_invoice_issue_corp WHERE sum_invoice_amount > ?"
)

var logisticsConnStr string
var socialConnStr string

func InitOrm(runmode string) {
	if runmode != "prod" {
		orm.Debug = true
		if runmode == "uat" {
			logisticsConnStr = "prod_klydb:@WW%_)2a@tcp(rm-bp10j3s1681i41x20.mysql.rds.aliyuncs.com:3306)/KlyDB?charset=utf8"
			socialConnStr = "prod_klydb:@WW%_)2a@tcp(rm-bp10j3s1681i41x20.mysql.rds.aliyuncs.com:3306)/Kly_SocialDB?charset=utf8"
			//logisticsConnStr = "uat:@WW%_)2a@tcp(121.199.0.64:22479)/KlyDB?charset=utf8"
			//socialConnStr = "uat:@WW%_)2a@tcp(121.199.0.64:22479)/Kly_SocialDB?charset=utf8"
		} else if runmode == "test" {
			logisticsConnStr = "test:123456@tcp(120.26.223.135:22479)/KlyDB?charset=utf8"
			socialConnStr = "test:123456@tcp(120.26.223.135:22479)/Kly_SocialDB?charset=utf8"
		} else {
			logisticsConnStr = "root:123456@tcp(127.0.0.1:3306)/KlyDB?charset=utf8"
			socialConnStr = "root:123456@tcp(127.0.0.1:3306)/Kly_SocialDB?charset=utf8"
		}
	}

	orm.DebugLog = orm.NewLog(util.UseBeeLogger())
	orm.RegisterDriver("mysql", orm.DRMySQL)
	orm.RegisterDataBase("default", "mysql", logisticsConnStr)
	orm.RegisterDataBase("social", "mysql", socialConnStr)
}

func GetLogisticsConnStr() string {
	return logisticsConnStr
}

func GetSocialConnStr() string {
	return socialConnStr
}

func SqlExec(cmd string) error {
	db, err := sql.Open("mysql", logisticsConnStr)
	if err != nil {
		return err
	}
	defer db.Close()
	_, err = db.Exec(cmd)
	return err
}
